/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bd;

import beans.Usuario;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Sherman
 */
public class DBConnection {

    private Connection db;
    private Statement stat;
    private PreparedStatement pStat;
    private ResultSet rs;

    public DBConnection() {


        String nomeDriver = "org.postgresql.Driver";
        try {
            Class.forName(nomeDriver);
        } catch (ClassNotFoundException ex) {
            //Logger.getLogger(ConexaoPgSQL.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("Classe nÃ£o exitente - Driver " + nomeDriver + "\n" + ex.getLocalizedMessage());
            ex.printStackTrace();
        }

    }

    public void Conecta(String servidor, String porta, String banco, String user, String pwd) throws SQLException {

        String strcon = "jdbc:postgresql://" + servidor + ":" + porta + "/" + banco;

        System.out.println("Connecting to Database URL = " + strcon);

        this.db = DriverManager.getConnection(strcon, user, pwd);
        this.stat = db.createStatement();
    }

    public boolean cadastraUsuarioFisico(Usuario user) {
        System.out.println("agora vamo ve = " + user.getNome());
        String insertUsuarioFisico = "INSERT INTO usuariofisico(nome,cpf,endereco,numero,bairro,cep,cidade,estado) VALUES(?,?,?,?,?,?,?,?)";
        int num = Integer.parseInt(user.getNumero());

        try {
            this.pStat = this.db.prepareStatement(insertUsuarioFisico);
            this.pStat.setString(1, user.getNome());

            this.pStat.setString(2, user.getCpf());
            this.pStat.setString(3, user.getEndereco());
            this.pStat.setInt(4, num);
            this.pStat.setString(5, user.getBairro());
            this.pStat.setString(6, user.getCep());
            this.pStat.setString(7, user.getCidade());
            this.pStat.setString(8, user.getEstado());

            this.pStat.execute();
            this.pStat.close();

            return true;
        } catch (SQLException ex) {
            Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }

    }

    public ArrayList retornaDadosArvore() throws SQLException {
        ArrayList dadosArvore = new ArrayList();

        String consulta = "SELECT familia,nome_cientifico,nome_popular,altura FROM \"Arvore\"";


        this.rs = this.stat.executeQuery(consulta);

        if (this.rs != null) {

            while (this.rs.next()) {
                // dadosArvore.add(new Arvores(rs.getString(1), rs.getString(2), rs.getString(3), rs.getFloat(4)));
            }

        }

        return dadosArvore;
    }

    public ArrayList buscaPorFamilia(String familia) throws SQLException {
        System.out.println("teste");
        ArrayList dadosArvore = new ArrayList();

        String consulta = "SELECT familia,nome_cientifico,nome_popular,altura FROM \"arvore\" WHERE familia like '%" + familia + "%'";


        this.rs = this.stat.executeQuery(consulta);

        if (this.rs != null) {

            while (this.rs.next()) {
                // dadosArvore.add(new Arvores(rs.getString(1), rs.getString(2), rs.getString(3), rs.getFloat(4)));
            }

        }

        return dadosArvore;

    }
}
